USE [texchange_db]
GO

/****** Object:  UserDefinedFunction [dbo].[Fn_CompById]    Script Date: 05/29/2011 19:53:20 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE FUNCTION [dbo].[Fn_CompById] 
(	
	-- Add the parameters for the function here
	@id  bigint=0,
	@language_id bigint = 0
)
RETURNS TABLE 
AS
RETURN 
(
	-- Add the SELECT statement with parameter references here
	SELECT Company.id,
	Company.name,
	Company.country_id,
	Company.City,
	Company.postcode,
	Company.[address],
	Company.type_company_id,
	Company.operating_activity,
	Company.telephone,
	Company.ext,
	Company.fax,
	Company.mobile,
	Company.website,
	Company.contact_person,
	Company.[language],
	Company.job_title,
	Company.email,
	Company.mobile_phone,
	Company.tax_file_number,
	Company.operating_licence,
	Company.company_status,
	Company.member_type_id,
	Company.created,
	Places.name as country, CompanyMemberType.name as memeber,
	Company.fileimage
	FROM Company
	LEFT JOIN Places ON Company.country_id = Places.id 
	LEFT JOIN CompanyMemberType ON Company.member_type_id = CompanyMemberType.id 
	WHERE Company.id = @id
)

--SELECT * FROM Fn_CompById(2,1)
GO


